'''
Created on Jul 6, 2012

@author: xding
'''

from sqllib import graph_table_list
from graphlib import globalmap
from shortcuts import execute_sql, execute_select, execute_insert, log_info


def build_graph_nodes(dataset_name):
    log_info("build graph nodes (group nearby stops < 16m) " + dataset_name)
    execute_sql("DELETE FROM %s_graph_nodes" % dataset_name)
    
    stops = execute_select("SELECT dataset_id, stop_id, stop_lat, stop_lon FROM %s_stops" % dataset_name)
    nodes = {}
    stop_to_node = {}
    for stop in stops:
        dataset_id, stop_id, stop_lat, stop_lon = stop[:4]
        precision = 1e+5
        ''' we store coordinates as integer: 120.375292 -> 12037529 '''
        latitude  = int(round(float(stop_lat) * precision))
        longitude = int(round(float(stop_lon) * precision))
        tile_id = globalmap.calculate_tile_id(latitude, longitude)
        if (latitude, longitude) not in nodes:
            inputs = [("latitude", latitude), ("longitude", longitude), ("tile_id", tile_id)]
            node_id = execute_insert(dataset_name + "_graph_nodes", inputs)
            nodes[(latitude, longitude)] = node_id
        node_id = nodes[(latitude, longitude)]
        stop_to_node[stop_id] = node_id
        sql = """
            UPDATE %s_stops SET graph_node_id=%d WHERE dataset_id=%d AND stop_id='%s'
        """ % (dataset_name, node_id, dataset_id, stop_id)
        execute_sql(sql)
    execute_sql("commit")
    return stop_to_node


def get_graph_route_id(stop_list, graph_route_tree, graph_route_num):
    _XXX_ = "___not_a_stop_but_a_route_id___"
    p = graph_route_tree
    for stop in stop_list:
        if stop not in p:
            p[stop] = dict()
        p = p[stop]
    if _XXX_ in p:
        return p[_XXX_], graph_route_num, False
    else:
        p[_XXX_] = graph_route_num + 1
        return graph_route_num + 1, graph_route_num + 1, True


def build_graph(dataset_name):
    for graph_table in graph_table_list:
        execute_sql("DELETE FROM %s_%s" % (dataset_name, graph_table))
    
    stop_to_graph_node = build_graph_nodes(dataset_name)
    
    current_gtfs_route = None
    graph_route_counter = 0
    
    log_info("build graph " + dataset_name)
    
    trips = execute_select("SELECT dataset_id, route_id, trip_id FROM %s_trips ORDER BY dataset_id, route_id" % dataset_name)
    for (i, trip) in enumerate(trips):
        if i % 1000 == 0:
            log_info(" - %s_graph %d" % (dataset_name, i))
        
        dataset_id, route_id, trip_id = trip[:3]
        if route_id != current_gtfs_route:
            current_gtfs_route = route_id
            graph_route_tree = dict()
            route_arc_sequence = dict()
        
        stop_times = execute_select("""
            SELECT stop_id, TIME_TO_SEC(arrival_time) as arrival_time, TIME_TO_SEC(departure_time) as departure_time
            FROM %s_stop_times WHERE dataset_id = %d AND trip_id = '%s'
            ORDER BY stop_sequence
        """ % (dataset_name, dataset_id, trip_id))
        stop_list = [item[0] for item in stop_times]
        arc_list = [(dep_item[0], arr_item[0], dep_item[2], arr_item[1])
                    #dep_stop     arr_stop     dep_time     arr_time
                    for (dep_item, arr_item) in zip(stop_times[:-1], stop_times[1:])]
        
        if len(stop_times) >= 2:
            graph_route_id, graph_route_counter, new_route_found = get_graph_route_id(stop_list, graph_route_tree, graph_route_counter)
            if new_route_found:
                inputs = [("route_id", graph_route_id), ("gtfs_route_id", route_id)]
                execute_insert(dataset_name + "_graph_routes", inputs)
                route_arc_sequence[graph_route_id] = sequence = []
                for (dep_stop, arr_stop, dep_time, arr_time) in arc_list:
                    inputs = [("dep_node", stop_to_graph_node[dep_stop]), ("arr_node", stop_to_graph_node[arr_stop]),
                              ("route", graph_route_id), ("cost", arr_time - dep_time)]
                    arc_id = execute_insert(dataset_name + "_graph_arcs", inputs)
                    sequence.append(arc_id)
            #end new route insert
            sql = """
                UPDATE %s_trips SET graph_route_id=%d WHERE dataset_id=%d AND trip_id='%s'
            """ % (dataset_name, graph_route_id, dataset_id, trip_id)
            execute_sql(sql)
            for ((dep_stop, arr_stop, dep_time, arr_time), arc_id) in zip(arc_list, route_arc_sequence[graph_route_id]):
                inputs = [("arc_id", arc_id), ("dep_time", dep_time), ("arr_time", arr_time), ("days", 0xFFFFFFF),]
                execute_insert(dataset_name + "_graph_timetable", inputs)
            execute_sql("commit")
        #end trip insert
    #end for all trips
    inputs = [("route_id", 0), ("gtfs_route_id", "__walk__")]
    execute_insert(dataset_name + "_graph_routes", inputs)

